
/*
 *  
 *
 *  Copyright (C) Microsoft Corporation.  All rights reserved
 *
 *  
 */


using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Core = Microsoft.Office.Core;
using System.Windows.Forms;
using System.Collections;
using Microsoft.VisualStudio.Tools.Applications.Runtime;

namespace CRMScenarios
{
    /// <summary>
    /// Helper routines for handling with the excel files
    /// </summary>
    static class ExcelHelper
    {
        static Excel.Application application = null;
        static Excel.AppEvents_Event appEvents = null;
        static Core.CommandBar toolBar = null;
        static Core.CommandBarButton insFieldBtn = null;
        static Core.CommandBarButton attachDocBtn = null;

        //Smart Search Controls
        static Core.CommandBar[] ContexMenuCommandBars = null;
        static Core.CommandBarPopup[] ContextMenuItems = null;
        static Core.CommandBarButton[] PopUpEntities = null;
        static bool fPassedOnce = false;


        /// <summary>
        /// actions to be taken on click of the button. this does 
        /// preparing the document readyt for attaching the assembly and 
        /// makes the attach call
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="scenario"></param>
        public static int Initialization(Excel.Application app)
        {
            object missing = System.Reflection.Missing.Value;

            if (app != null)
            {
                application = app;
            }


            ContexMenuCommandBars = new Microsoft.Office.Core.CommandBar[OfficeApi.ContextMenuNamesForExcel.Length];
            ContextMenuItems = new Microsoft.Office.Core.CommandBarPopup[OfficeApi.ContextMenuNamesForExcel.Length];
            int iCommandBarCount = 0;
            try
            {

                foreach (String strMenuName in OfficeApi.ContextMenuNamesForExcel)
                    ContexMenuCommandBars[iCommandBarCount++] = (Core.CommandBar)application.CommandBars[strMenuName];
            }
            catch (Exception exp)
            {
            }



            try
            {
                for (int iCount = 0; iCount < iCommandBarCount; iCount++)
                {
                    try
                    {
                        ContextMenuItems[iCount] = (Core.CommandBarPopup)ContexMenuCommandBars[iCount].Controls["Search in CRM"];
                        ContextMenuItems[iCount].Delete(missing);
                    }
                    catch { }
                }
                try
                {
                    for (int iCount = 0; iCount < iCommandBarCount; iCount++)
                    {
                        ContextMenuItems[iCount] = (Core.CommandBarPopup)ContexMenuCommandBars[iCount].Controls.Add(Core.MsoControlType.msoControlPopup, missing, missing, missing, missing);
                        ContextMenuItems[iCount].Caption = "Search in CRM";
                        ContextMenuItems[iCount].Tag = "Search in CRM";
                        ArrayList aListAxEntities = new ArrayList();
                        aListAxEntities = Helper.GetCRMEntities();
                        
                        if (aListAxEntities == null)
                            return -1;

                        int iEntityIndex = 0;
                        PopUpEntities = new Microsoft.Office.Core.CommandBarButton[aListAxEntities.Count + 1];
                        //Take first 4 entities or recently accessed 4 TODO
                        foreach (String strEntityName in aListAxEntities)
                        {
                            PopUpEntities[iEntityIndex] = (Core.CommandBarButton)ContextMenuItems[iCount].Controls.Add(Core.MsoControlType.msoControlButton, missing, missing, missing, missing);
                            PopUpEntities[iEntityIndex].Style = Core.MsoButtonStyle.msoButtonCaption;
                            PopUpEntities[iEntityIndex].Caption = strEntityName;
                            PopUpEntities[iEntityIndex].Tag = strEntityName;
                            PopUpEntities[iEntityIndex].Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(btnEntitySpecific_Click);
                            iEntityIndex++;
                        }
                        PopUpEntities[iEntityIndex] = (Core.CommandBarButton)ContextMenuItems[iCount].Controls.Add(Core.MsoControlType.msoControlButton, missing, missing, missing, missing);
                        PopUpEntities[iEntityIndex].Style = Core.MsoButtonStyle.msoButtonCaption;
                        PopUpEntities[iEntityIndex].Caption = "More...";
                        PopUpEntities[iEntityIndex].Tag = "More...";
                        PopUpEntities[iEntityIndex].Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(btnEntitySpecific_Click);
                        iEntityIndex++;

                    }
                }
                catch (Exception innerExp)
                {
                    MessageBox.Show(innerExp.Message);
                    return -1;
                }

            }
            catch
            {
            }





            try
            {
                toolBar = (Core.CommandBar)application.CommandBars[global::CRMScenarios.Resources.ToolBarName];
            }
            catch (Exception)
            {

                try
                {
                    toolBar = (Core.CommandBar)application.CommandBars.Add(global::CRMScenarios.Resources.ToolBarName, 1, missing, true);
                }
                catch (Exception )
                {
                    Util.ShowError(global::CRMScenarios.Resources.ToolBarCreateFailureMsg);
                    return -1;
                }
            }

            try
            {
                insFieldBtn = (Core.CommandBarButton)toolBar.Controls[global::CRMScenarios.Resources.InsertButtonCaption]; // TODO
                attachDocBtn = (Core.CommandBarButton)toolBar.Controls[global::CRMScenarios.Resources.AttachButtonCaption];  // TODO
            }
            catch (Exception )
            {
                try
                {
                    insFieldBtn = (Core.CommandBarButton)toolBar.Controls.Add(1, missing, missing, missing, missing);
                    attachDocBtn = (Core.CommandBarButton)toolBar.Controls.Add(1, missing, missing, missing, missing);
                }

                catch (Exception )
                {
                    Util.ShowError(global::CRMScenarios.Resources.DialogBoxTitle);
                    return -1;
                }
            }


            if ((toolBar != null))
            {
                toolBar.Protection = Microsoft.Office.Core.MsoBarProtection.msoBarNoProtection;
                if (insFieldBtn != null)
                {        
                    insFieldBtn.Style = Core.MsoButtonStyle.msoButtonIconAndCaption;
                    insFieldBtn.Caption = global::CRMScenarios.Resources.InsertButtonCaption;
                    insFieldBtn.Tag = global::CRMScenarios.Resources.InsertButtonCaption;
                    insFieldBtn.Click += new Core._CommandBarButtonEvents_ClickEventHandler(insFieldBtn_Click);
                    Clipboard.SetDataObject(global::CRMScenarios.Resources.ico_insertfromcrm1);
                    insFieldBtn.PasteFace();
                    Clipboard.Clear();
                }

                if (attachDocBtn != null)
                {
                    attachDocBtn.Style = Core.MsoButtonStyle.msoButtonIconAndCaption;
                    attachDocBtn.Caption = global::CRMScenarios.Resources.AttachButtonCaption;
                    attachDocBtn.Tag = global::CRMScenarios.Resources.AttachButtonCaption;
                    attachDocBtn.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(attachDocBtn_Click);
                    Clipboard.SetDataObject(global::CRMScenarios.Resources.ico_attachtocrm1);
                    attachDocBtn.PasteFace();
                    Clipboard.Clear();

                }

                toolBar.Enabled = true;
                toolBar.Visible = true;
                
            }
            else
            {
                Util.ShowError(global::CRMScenarios.Resources.ToolBarCreateFailureMsg);
            }

            try
            {
                application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(application_WorkbookOpen);
                app.WorkbookNewSheet += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookNewSheetEventHandler(app_WorkbookNewSheet);

                application.WorkbookBeforeClose += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeCloseEventHandler(application_WorkbookBeforeClose);

                ((Excel.AppEvents_Event)application).NewWorkbook += new Microsoft.Office.Interop.Excel.AppEvents_NewWorkbookEventHandler(ExcelHelper_NewWorkbook);
                ((Excel.AppEvents_Event)application).SheetSelectionChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetSelectionChangeEventHandler(ExcelHelper_SheetSelectionChange);
                ((Excel.AppEvents_Event)application).SheetBeforeDoubleClick += new Microsoft.Office.Interop.Excel.AppEvents_SheetBeforeDoubleClickEventHandler(ExcelHelper_SheetBeforeDoubleClick);
            }
            catch (Exception )
            {
                Util.ShowError(global::CRMScenarios.Resources.EventHandlingErr);
                cleanUpApplication();
                return -1;
            }
            return 0;
        }

        static void ExcelHelper_SheetBeforeDoubleClick(object Sh, Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
        {
            fPassedOnce = false;
        }

        static void ExcelHelper_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
        {
            fPassedOnce = false;
        }

        static void btnEntitySpecific_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            if(!fPassedOnce)
            {
                Excel.Workbook book = application.ActiveWorkbook;

                if (book.ReadOnly)
                {
                    MessageBox.Show(global::CRMScenarios.Resources.DocReadOnlyMessage);
                    return;
                }

                Excel.Range objSelection = application.ActiveCell;
                String strValuesSelected = string.Empty;
                strValuesSelected += "*";
                strValuesSelected += objSelection.Text.ToString();
                strValuesSelected = strValuesSelected.Trim();
                strValuesSelected += "*";
                Core.DocumentProperties docProperties = null;

                try
                {

                    //Adding Custom Property
                    OfficeApi.RemoveCustomProperty(book, "Selected Values");
                    OfficeApi.SetCustomProperty(book, strValuesSelected, "Selected Values");
                    OfficeApi.RemoveCustomProperty(book, "Selected Entity");
                    OfficeApi.SetCustomProperty(book, Ctrl.Caption, "Selected Entity");
                    
                }
                catch { }

                if (OfficeApi.GetCustomProperty(book, "CRMVSTO") != null)
                {
                    // Show actionsPane for CRM.
                    application.Toolbars["Task Pane"].Visible = true;
                    return;
                }
                ArrangeAndDoAddCustomizationExcel(book, 3); // 3 is for Multi Select
                OfficeApi.SetCustomProperty(book, "3", "CRMVSTO");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Wb"></param>
        static void ExcelHelper_NewWorkbook(Microsoft.Office.Interop.Excel.Workbook Wb)
        {
            toolBar.Enabled = true;
            toolBar.Visible = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Wb"></param>
        /// <param name="Sh"></param>
        static void app_WorkbookNewSheet(Microsoft.Office.Interop.Excel.Workbook Wb, object Sh)
        {
                toolBar.Enabled = true;
                toolBar.Visible = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Wb"></param>
        /// <param name="Cancel"></param>
        static void application_WorkbookBeforeClose(Microsoft.Office.Interop.Excel.Workbook Wb, ref bool Cancel)
        {
            if (application.Workbooks.Count == 1)
            {
                toolBar.Enabled = false;
            }
        }

        /// <summary>
        /// actions to be taken on click of the button. this does 
        /// preparing the document readyt for attaching the assembly and 
        /// makes the attach call
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="scenario"></param>
        private static void  ArrangeAndDoAddCustomizationExcel(Excel.Workbook book, int scenario)
        {
            if (Helper.IsTempFile(book.Path))
            {
                MessageBox.Show(global::CRMScenarios.Resources.TemporaryFileWarning);
                return;
            }
            string installationPath = Helper.GetInstallPath(string.Empty);
            string locationOfassembly = installationPath + @"Assembly\Microsoft.Crm.Office.Excel.application";
            closeAttachReOpen(book, locationOfassembly, scenario);
        }

        /// <summary>
        /// actions to be taken on click of the button.
        /// </summary>
        /// <param name="Ctrl"></param>
        /// <param name="CancelDefault"></param>
        static void attachDocBtn_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Excel.Workbook book = application.ActiveWorkbook;


            if (book.ReadOnly)
            {
                MessageBox.Show(global::CRMScenarios.Resources.DocReadOnlyMessage);
                return;
            }

            if (OfficeApi.GetCustomProperty(book, "CRMVSTO") != null)
            {
                application.Toolbars["Task Pane"].Visible = true;
                return;
            }

            // We reach here only if the book is writable, and non-crm-ified.
            // should do all proper error handling.
            ArrangeAndDoAddCustomizationExcel(book, 2); // 2 is for attach doc
        }

        /// <summary>
        /// actions to be taken on click of the button.
        /// </summary>
        /// <param name="Ctrl"></param>
        /// <param name="CancelDefault"></param>
        static void insFieldBtn_Click(Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Excel.Workbook book = application.ActiveWorkbook;

            if (book.ReadOnly)
            {
                MessageBox.Show(global::CRMScenarios.Resources.DocReadOnlyMessage);
                return;
            }

            if (OfficeApi.GetCustomProperty(book, "CRMVSTO") != null)
            {
                // Show actionsPane for CRM.
                application.Toolbars["Task Pane"].Visible = true;
                return;
            }
            ArrangeAndDoAddCustomizationExcel(book, 1); // 1 is for insField doc
        }

        /// <summary>
        /// Attaching the assembly available at path to the document and make sure that the 
        /// scenario needed will come once the documetn is opened.
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="pathToAssembly"></param>
        /// <param name="scenario"></param>
        static void closeAttachReOpen(Excel.Workbook book, string pathToAssembly, int scenario)
        {
            object o = System.Reflection.Missing.Value;
            string partialPath = null;
            string bookName = null;
            string fullPath = null;
            object objfullPath = null;
            bool cont = true;

            if ((book == null) || (pathToAssembly == null))
            {
                return;
            }
            if (!OfficeApi.IsDocumentSaved(book))
            {
                DialogResult okayCancel = Util.AskQues(global::CRMScenarios.Resources.AttachOrNotQuestionMsg);

                if (okayCancel == DialogResult.Cancel)
                {
                    fPassedOnce = true;
                    return;
                }
            }
            /* 
            * Add custom properties to the document which makes sure that the assembly is added.
            * and on failure clean the document.
            * 
            * if the assembly location is more than 255 chars, split it and add in two custom properties.
            */
            try
            {
                OfficeApi.SetCustomProperty(book, Convert.ToString(scenario, 10), "CRMVSTO");
                OfficeApi.SetCustomProperty(book, "*", "_AssemblyName");
                if (pathToAssembly.Length < 255)
                {
                    OfficeApi.SetCustomProperty(book, pathToAssembly, "_AssemblyLocation");
                }
                else // TODO : if path more than 512?
                {
                    string strPathFirst = pathToAssembly.Substring(0, 255);
                    string strPathSecond = pathToAssembly.Substring(255, pathToAssembly.Length);
                    OfficeApi.SetCustomProperty(book, strPathFirst, "_AssemblyLocation0");
                    OfficeApi.SetCustomProperty(book, strPathSecond, "_AssemblyLocation1");
                }
                cont = true;
            }
            catch
            {
                // UNDO THE ABOVE AND RETURN
                cont = false;
                OfficeApi.RemoveCustomProperty(book, "_AssemblyLocation");
                OfficeApi.RemoveCustomProperty(book, "_AssemblyLocation0");
                OfficeApi.RemoveCustomProperty(book, "_AssemblyLocation1");
                OfficeApi.RemoveCustomProperty(book, "CRMVSTO");
            }

            try
            {
                object sC = Excel.XlSaveAction.xlSaveChanges;
                Excel.XlSaveConflictResolution conflictResolution = Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution;
                try
                {
                    book.SaveAs(o, o, o, o, o, o, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, conflictResolution, o, o, o, o); // why this if anyway closing with saveChanges option?
                }
                catch
                {
                    MessageBox.Show(global::CRMScenarios.Resources.FileInUseWarning);
                    OfficeApi.RemoveCustomProperty(book, "CRMVSTO");
                    fPassedOnce = true;
                    return;
                }
                if (!cont)
                {
                    Util.ShowMsg(global::CRMScenarios.Resources.VSTOAttachErrorMsg);
                    fPassedOnce = true;
                    return;
                }
                partialPath = book.Path;
                bookName = book.Name;
                fullPath = partialPath + "\\" + bookName;
                objfullPath = (object)fullPath;
                book.Close(sC, o, o);
            }
            catch
            {
                Util.ShowError(global::CRMScenarios.Resources.ErrorWhileClosing);
            }


            /* 
             * now all the properties are added and the document is closed. Let's open the document
             * and things will work fine
             */

            try
            {
                book = application.Workbooks.Open(fullPath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
            }
            catch
            {
                Util.ShowError(global::CRMScenarios.Resources.DocOpenErrMsg);
            }
            fPassedOnce = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Wb"></param>
        static void application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
        {
            if (!toolBar.Enabled)
            {
                toolBar.Enabled = true;
            }
            if (!toolBar.Visible)
            {
                toolBar.Visible = true;
            }
        }

        /// <summary>
        /// Do a clean up action before exiting after all the work.
        /// It is always a good practice to unsubscribe the events, release the 
        /// resources, delete the buttons etc.
        /// </summary>
        public static void cleanUpApplication()
        {
            object missing = System.Reflection.Missing.Value;
            if (toolBar != null)
            {
                if (insFieldBtn != null)
                {
                    insFieldBtn.Click -= new Core._CommandBarButtonEvents_ClickEventHandler(insFieldBtn_Click);
                    // insFieldBtn.Delete(missing);
                }

                toolBar.Delete();
            }
            try
            {
                foreach (Core.CommandBar cmdBarContexMenu in ContexMenuCommandBars)
                {
                    Core.CommandBarPopup cntrlTemp = (Core.CommandBarPopup)cmdBarContexMenu.Controls["Search in CRM"];
                    foreach (Core.CommandBarControl cntrl in cntrlTemp.Controls)
                    {
                        cntrl.Delete(missing);
                    }
                    cntrlTemp.Delete(missing);
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
//                ActionsPaneControl1.ShowErrorWarning(exp.Message, ActionsPaneControl1.AxMessageTypes.ERROR);
            }


            try
            {

                application.WorkbookOpen -= new Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(application_WorkbookOpen);
                appEvents.NewWorkbook -= new Microsoft.Office.Interop.Excel.AppEvents_NewWorkbookEventHandler(application_WorkbookOpen);
            }
            catch (Exception exp)
            {
                Helper.Trace("Excel CleanUP\n" + exp.StackTrace, false);
            }

        }       
    }
}

